/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50144
 Source Host           : localhost
 Source Database       : p

 Target Server Type    : MySQL
 Target Server Version : 50144
 File Encoding         : utf-8

 Date: 08/22/2012 09:46:02 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `allocation_items`
-- ----------------------------
DROP TABLE IF EXISTS `allocation_items`;
CREATE TABLE `allocation_items` (
  `code` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `allocation_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT '1' COMMENT '1',
  `remark` text CHARACTER SET utf8,
  `return_date` datetime DEFAULT NULL,
  `return` int(11) DEFAULT NULL COMMENT '0',
  `category_id` int(11) NOT NULL,
  `allocation_item_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`allocation_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `allocation_items`
-- ----------------------------
BEGIN;
INSERT INTO `allocation_items` VALUES (null, '1', '5', '', null, null, '10', '1');
COMMIT;

-- ----------------------------
--  Table structure for `allocations`
-- ----------------------------
DROP TABLE IF EXISTS `allocations`;
CREATE TABLE `allocations` (
  `allocation_id` int(11) NOT NULL AUTO_INCREMENT,
  `allocation_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `return_date` date DEFAULT NULL,
  `allocation_type` varchar(255) DEFAULT NULL,
  `place_id` int(11) NOT NULL,
  PRIMARY KEY (`allocation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `allocations`
-- ----------------------------
BEGIN;
INSERT INTO `allocations` VALUES ('1', '2012-08-17', '2', 'แจก น.ส', null, null, '1');
COMMIT;

-- ----------------------------
--  Table structure for `budgets`
-- ----------------------------
DROP TABLE IF EXISTS `budgets`;
CREATE TABLE `budgets` (
  `budget_id` int(11) NOT NULL AUTO_INCREMENT,
  `budget_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชืองบประมาณ สำหรับการจัดซื้อ',
  `year` varchar(255) DEFAULT NULL,
  `remark` text,
  PRIMARY KEY (`budget_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `budgets`
-- ----------------------------
BEGIN;
INSERT INTO `budgets` VALUES ('1', 'งบประมาณประจำปี', '2554', null), ('2', 'งบประมาณประจำปี', '2555', null);
COMMIT;

-- ----------------------------
--  Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `address` text CHARACTER SET utf8,
  `tel` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `fax` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `company`
-- ----------------------------
BEGIN;
INSERT INTO `company` VALUES ('1', 'Cyberland Pacific Co.,Ltd.', '40/245 หมู่ 7 ถ.พระยาสุเรนทร์ แขวงสามวาตะวันตก เขตคลองสามวา กรุงเทพมหานคร 10510', '0-2914-2942', '0-2908-0945', 'patchaya@cyberland.co.th'), ('2', 'ร้านมาสเตอร์ สาขา ตรงข้ามเซ็นทรัลลาดพร้าว', '206/1 ถนนพหลโยธิน แขวงลาดยาว เขตจตุจักร กรุงเทพมหานคร 10900\nhttp://www.masterphotonetwork.com', '085-825-7510', '0-2930-9389', ''), ('3', 'work progression co.ltd', '199/22 หมู่ 6 แขวงหลักสอง เขตบางแค กทม.10160\nhttp://workprogression.com', '02-8080437', '02-8080437', ''), ('4', 'แกรนด์ฟิสค์ จำกัด', '13,15 ซ.อ่อนนุช 70/1 ถ.อ่อนนุช แขวงประเวศ เขตประเวศ กรุงเทพมหานคร 10250', '02-3202242-9', '02-3202242-9', 'grandfisc@hotmail.com'), ('5', 'บริษัท โฮม โปรดักส์ เซ็นเตอร์ จำกัด (มหาชน)', '96/27 หมู่ 9 ตำบลบางเขน อำเภอเมือง นนทบุรี', '0-2832-1000', '0-2832-1400', '');
COMMIT;

-- ----------------------------
--  Table structure for `controller_groups`
-- ----------------------------
DROP TABLE IF EXISTS `controller_groups`;
CREATE TABLE `controller_groups` (
  `c_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL,
  PRIMARY KEY (`c_group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
--  Records of `controller_groups`
-- ----------------------------
BEGIN;
INSERT INTO `controller_groups` VALUES ('1', 'จัดการผู้ใช้งาน', '4'), ('2', 'จัดการข้อมูลระบบ', '3'), ('3', 'ข้อมูลสรุป&รายงาน', '2'), ('4', 'ลงทะเบียนวัสดุ-ครุภัณฑ์', '1'), ('5', 'ข้อมูลส่วนตัว', '5'), ('6', 'ค้นหา', '0'), ('7', 'งานซ่อม', '4'), ('8', 'ตรวจสถาการใช้งาน', '6'), ('9', 'สำรวจความต้องการ', '4'), ('10', 'Developer', '7'), ('11', 'เบิกจ่ายวัสดุ-ครุภัณฑ์', '2'), ('12', 'สังซื้อวัสดุ-ครุภัณฑ์', '2');
COMMIT;

-- ----------------------------
--  Table structure for `controllers`
-- ----------------------------
DROP TABLE IF EXISTS `controllers`;
CREATE TABLE `controllers` (
  `c_id` int(255) NOT NULL AUTO_INCREMENT,
  `controller` varchar(255) NOT NULL,
  `detail` varchar(255) NOT NULL,
  `sort_order` varchar(255) NOT NULL,
  `c_group_id` int(255) NOT NULL,
  `is_menu` varchar(255) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=MyISAM AUTO_INCREMENT=113 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
--  Records of `controllers`
-- ----------------------------
BEGIN;
INSERT INTO `controllers` VALUES ('1', 'admin/users', 'รายการผู้ใช้งาน', '', '1', '1'), ('2', 'admin/user_add', 'เพิ่มผู้ใช้งานใหม่', '', '1', '1'), ('3', 'admin/user_edit', 'แก้ไข้ผู้ใช้งาน', '', '1', '0'), ('4', 'admin/pass_edit', 'เปลี่ยนรหัสผู้ใช้งาน', '', '1', '0'), ('5', 'admin/user_roles', 'รายการกลุ่มผู้ใช้งาน', '', '1', '1'), ('6', 'admin/user_role_add', 'เพิ่มกลุ่มผู้ใช้งานใหม่', '', '1', '0'), ('7', 'admin/role_edit', 'แก้ไข้กลุ่มผู้ใช้งาน', '', '1', '0'), ('8', 'admin/user_role_delete', 'ลบกลุ่มผู้ใช้งาน', '', '1', '0'), ('29', 'admin/material_add2', 'ลงทะเบียนครุภัณฑ์ใหม่', '', '4', '1'), ('30', 'admin/material_add1', 'ลงทะเบียนวัสดุใหม่', '', '4', '1'), ('31', 'admin/categories', 'หมวดวัสดุ-ครุภัณฑ์', '', '2', '1'), ('32', 'admin/category_add', 'เพิ่มหมวด วัสดุ-ครุภัณฑ์ ใหม่', '', '2', '0'), ('33', 'admin/category_edit', 'แก้ไขหมวด วัสดุ-ครุภัณฑ์', '', '2', '0'), ('34', 'admin/company', 'ข้อมูลบริษัท', '', '2', '1'), ('35', 'admin/company_add', 'เพิ่มข้อมูลบริษัท ใหม่', '', '2', '0'), ('36', 'admin/company_edit', 'แก้ไขข้อมูลบริษัท', '', '2', '0'), ('37', 'admin/room', 'ข้อมูลห้องปฏิบัติการ/สถานที่', '', '2', '1'), ('38', 'admin/room_add', 'เพิ่มข้อมูลห้องปฏิบัติการ/สถานที่', '', '2', '0'), ('39', 'admin/room_edit', 'แก้ไขข้อมูลห้องปฏิบัติการ/สถานที่', '', '2', '0'), ('40', 'admin/room_delete', 'ลบข้อมูลห้องปฏิบัติการ/สถานที่', '', '2', '0'), ('41', 'admin/pass_edit2', 'แก้ไขรหัสผ่าน', '', '5', '0'), ('42', 'admin/user_edit2', 'แก้ไขข้อมูลส่วนตัว', '', '5', '0'), ('44', 'admin/maintains', 'ข้อมูล ชนิดงานซ่อม', '', '7', '1'), ('45', 'admin/maintain_add', 'main tain type add', '', '7', '0'), ('46', 'admin/maintain_edit', 'บันทึกงานซ่อม', '', '7', '0'), ('47', 'report/material2_by_year', 'รายงาน ครุภัณฑ์ประจำปีการศึกษา', '', '3', '1'), ('48', 'report/material1_by_year', 'รายงาน วัสดุประจำปีการศึกษา', '', '3', '1'), ('49', 'admin/search', 'รายการ วัสดุ-ครุภัณฑ์ ลงทะเบียน', '', '4', '1'), ('51', 'report/exp_list', 'รายการ วัสดุ-ครุภัณฑ์หมดอายุการใช้งาน', '', '3', '1'), ('52', 'report/page5', 'รายงานความต้องการใช้ ครุภัณฑ์ในห้องปฏิบัติการ', '', '3', '0'), ('53', 'report/m_request_by_year1', 'รายงานความต้องการใช้วัสดุประจำภาคการศึกษา ', '', '3', '1'), ('54', 'report/m_request_by_year2', 'รายงานความต้องการใช้ครุภัณฑ์ประจำภาคการศึกษา ', '', '3', '1'), ('55', 'report/summary2', 'รายงานข้อมูลทะเบียนครุภัณฑ์ ', '', '3', '0'), ('56', 'report/summary', 'รายงานข้อมูลทะเบียน วัสดุ-ครุภัณฑ์ ', '', '3', '1'), ('57', 'report/budget2', 'รายงานการครุภัณฑ์ประจำงบประมาณ', '', '3', '1'), ('58', 'report/budget1', 'รายงานการวัสดุประจำงบประมาณ', '', '3', '1'), ('59', 'report/page9', 'รายงานสภาพการใช้งานครุภัณฑ์ ', '', '3', '0'), ('60', 'report/page10', 'รายงานการซ่อมบำรุงครุภัณฑ์', '', '3', '0'), ('61', 'report/maintain_company', 'รายงานการส่งซ่อมครุภัณฑ์', '', '3', '0'), ('62', 'report/exp_list', 'รายงานการจำหน่ายครุภัณฑ์หมดอายุการใช้งาน ', '', '3', '1'), ('64', 'admin/budgets', 'ข้อมูลงบประมาณ', '', '2', '1'), ('65', 'admin/material_view', 'รายละอียดวัสดุ/ครุภัฑฑ์ ลงทะเบียน', '', '2', '0'), ('66', 'admin/maintain_request', 'แจ้งซ้อมใหม่', '', '7', '1'), ('67', 'admin/maintain_request_list', 'รายการงานแจ้งซ่อม', '', '7', '1'), ('68', 'admin/maintain_request_process_list', 'รายการงานส่งซ่อม', '', '7', '0'), ('69', 'admin/maintain_request_finish_list', 'รายการงานซ่อมเสร็จแล้ว', '', '7', '0'), ('70', 'admin/maintain_detail', 'รายละเอียดงานซ่อม', '', '7', '0'), ('72', 'admin/m_maintain_add', 'บันทึกงานซ่อม', '', '7', ''), ('73', 'admin/maintain_finish', 'ปิดงานซ่อม', '', '7', ''), ('74', 'report/query_form', 'Query Builders', '', '10', '1'), ('75', 'admin/materail_edit', 'แก้ไข้วัสดุ', '', '2', ''), ('76', 'report/all_report', 'Query list', '', '10', '1'), ('77', 'admin/maintain_view', 'รายละเอียดงานซ่อม', '', '7', '0'), ('78', 'admin/maintain_request_delete', 'ลบใบแจ้งซ่อม', '', '7', ''), ('79', 'admin/m_request_add1', 'แบบฟอร์มสำรวจความต้องการวัสดุ', '2', '9', '1'), ('80', 'admin/m_request_list1', 'ข้อมูลสำรวจความต้องการวัสดุ', '3', '9', '1'), ('81', 'admin/role_delete', 'ลบกลุ่มผุ้ใช้งาน', '', '0', ''), ('82', 'admin/material_edit2', 'แก้ไขวัสดุ', '', '0', '1'), ('83', 'admin/material_add_item\r\nmaterial_add_item', 'เพิ่มรายการวัสดุ-ครุภัณฑ์', '', '7', '0'), ('84', 'admin/inventory_list', 'รายกาวัสดุ-ครุภัณฑ์ คงเหลือ', '', '11', '1'), ('85', 'admin/teacher_request', 'ขอเบิกวัสดุ-ครุภัณฑ์', '', '11', '1'), ('86', 'admin/request_list', 'รายการขอเบิก วัสดุ-ครุภัณฑ์', '', '11', '1'), ('87', 'admin/request_detail', 'รายละเอียดขอเบิก วัสดุ-ครุภัณฑ์', '', '11', ''), ('88', 'report/dashboard', 'สรุปข้อมูลในระบบ', '', '0', '0'), ('89', 'admin/budget_add', 'เพิ่มรายการงบประมาณใหม่', '', '2', ''), ('90', 'admin/item_update_status2', 'บันทึกวัสดุ-ครุภัณฑ์หมดอายุการใช้งาน', '', '4', '1'), ('91', 'admin/m_exp', 'รายการ วัสดุ-ครุภัณฑ์หมดอายุการใช้งาน', '', '2', '1'), ('92', 'admin/search_code', ' ค้นหาเลขที่วัสดุ-ครุภัณฑ์', '', '6', '1'), ('96', 'admin/budget_edit', 'แก้ไขข้อมูลงบประมาณ', '', '2', ''), ('93', 'admin/item_detail', 'รายละเอียดวัสดุ-ครุภัณฑ์', '', '2', ''), ('94', 'admin/m_request_detail', 'ข้อมูลสำรวจความต้องการใช้งาน', '', '9', '0'), ('95', 'admin/allocation_return', 'บันทึกคืน วัสดุ-ครุภัณฑ์', '', '11', '1'), ('97', 'admin/m_request_add2', 'แบบฟอร์มสำรวจความต้องการครุภัณฑ์', '0', '9', '1'), ('98', 'admin/m_request_list2', 'ข้อมูลสำรวจความต้องการครุภัณฑ์', '4', '9', '1'), ('99', 'admin/order_add1', 'สร้างใบสั่งซื้อวัสดุใหม่', '1', '12', '1'), ('100', 'admin/order_add2', 'สร้างใบสั่งซื้อครุภัณฑ์ใหม่', '2', '12', '1'), ('101', 'admin/order_list1', 'ข้อมูลใบสั่งซื้อ วัสดุ', '3', '12', '1'), ('102', 'admin/order_list2', 'ข้อมูลใบสั่งซื้อ ครุภัณฑ์', '4', '12', '1'), ('103', 'admin/order_delete2', 'ลบใบสั่งซื้อครุภัณฑ์', '', '12', '0'), ('104', 'admin/order_delete1', 'ลบใบส่ังซื้อวัสดุ', '', '12', '0'), ('105', 'admin/receive_order1', 'ตรวจรับวัสดุ', '', '12', '0'), ('106', 'admin/receive_order2', 'ตรวจรับครุภัณฑ์', '', '12', '0'), ('107', 'admin/order_to_regis1', 'ลงทะเบียนวัสดุจากใบสั่งซื้อ', '', '12', '0'), ('108', 'admin/order_to_regis2', 'ลงทะเบียนครุภัณฑ์จากใบสั่งซื้อ', '', '12', '0'), ('109', 'admin/request_return', 'คืนวัสดุ-ครุภัณฑ์', '', '11', ''), ('110', 'admin/material_delete', 'ลบข้อมูลลงทะเบียน วัสดุ-ครุภัณฑ์ ลงทะเบียน', '', '2', ''), ('111', 'admin/item_update_status3', 'บันทึกข้อมูลการจำหน่ายวัสดุ-ครุภัณฑ์ ', '', '4', '1'), ('112', 'admin/item_update_status1', 'ปรับสถานะครุภัณฑ์ใช้งานปกติ', '', '4', '1');
COMMIT;

-- ----------------------------
--  Table structure for `maintain_status`
-- ----------------------------
DROP TABLE IF EXISTS `maintain_status`;
CREATE TABLE `maintain_status` (
  `status_id` int(11) NOT NULL,
  `status_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `maintain_status`
-- ----------------------------
BEGIN;
INSERT INTO `maintain_status` VALUES ('1', 'แจ้งซ่อมใหม่'), ('2', 'กำลังดำเนินการ'), ('3', 'ซ่อมเสร็จแล้ว'), ('4', 'แจ้งซ่อมไม่ได้แล้วชำรุด');
COMMIT;

-- ----------------------------
--  Table structure for `maintains`
-- ----------------------------
DROP TABLE IF EXISTS `maintains`;
CREATE TABLE `maintains` (
  `maintain_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  `user_id` int(11) NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 NOT NULL,
  `remark` text CHARACTER SET utf8 NOT NULL,
  `status_id` int(11) NOT NULL,
  `create_date` datetime NOT NULL,
  `finish_date` date NOT NULL,
  `price` varchar(255) NOT NULL,
  `remark2` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `warranty` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`maintain_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `maintains`
-- ----------------------------
BEGIN;
INSERT INTO `maintains` VALUES ('1', 'บส102/2555', '9', 'คอมพิวเตอร์เปิดไม่ติด', 'ทดสอบ', '2', '2012-04-10 12:51:52', '2012-04-13', '1000', '', '1 เดือน'), ('2', 'บส103/2555', '9', 'คอมพิวเตอร์เปิดไม่ติด', 'เปิอเครื่องแล้วดับเองตลอดเลย', '1', '2012-04-15 13:20:07', '0000-00-00', '', '', ''), ('3', 'บส103/2555', '9', 'xxx', 'ปปป', '1', '2012-04-15 13:25:44', '0000-00-00', '', '', ''), ('4', 'บส131/2555', '9', 'เก้าอี้หัก', 'หักเนื่องจากหมูตอนขี้หลีนั่งเเตก--', '3', '2012-05-17 17:05:36', '2012-05-17', '100', 0xe0b983e0b8abe0b989e0b88ae0b988e0b8b2e0b887e0b894e0b8b9e0b981e0b8a5e0b989e0b8a7e0b984e0b8a1e0b988e0b895e0b8b4e0b894e0b89be0b8b1e0b88de0b8abe0b8b2e0b8ade0b8b0e0b984e0b8a3e0b981e0b8a5e0b989e0b8a7, '-'), ('5', 'บส103/2555', '12', 'คอมพิวเตอร์เปิดไม่ติด', 'เปิดคอมพิวเตอร์ไม่ติด', '1', '2012-06-07 20:13:34', '0000-00-00', '', '', ''), ('6', '1200117', '12', 'หนังสือชำรุด', 'หนังสือชำรุด โดนน้ำหกใส่', '3', '2012-08-19 20:53:44', '2012-08-19', '100', '', '');
COMMIT;

-- ----------------------------
--  Table structure for `material_categories`
-- ----------------------------
DROP TABLE IF EXISTS `material_categories`;
CREATE TABLE `material_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `cat_base_id` int(11) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_categories`
-- ----------------------------
BEGIN;
INSERT INTO `material_categories` VALUES ('2', 'คอมพิวเตอร์ตั้งโต๊ะ', '2', '2'), ('3', 'โต้ะคอมพิวเตอร์', '5', '2'), ('4', 'เครื่องปริ้นเตอร์ Laser', '2', '2'), ('5', 'ตู้เก็บของทั่วไป', '5', '2'), ('6', 'ปากกาเคมีสำหรับไวท์บอร์ด', '3', '1'), ('7', 'เครื่องปรับอากาศ', '1', '2'), ('8', 'อุปกรณ์สำหรับทำความสะอาด', '6', '1'), ('9', 'อุปกรณ์ขยายเสียง/ลำโพง', '1', '2'), ('10', 'หนังสือเรืยน สารสนเทศเพื่อชีวิต', '8', '1'), ('11', 'เครื่องถ่ายเอกสาร', '1', '2'), ('14', 'กระดาษ A4', '3', '1'), ('16', 'ลำโพง', '2', '2'), ('17', 'เทปกาว', '3', '1'), ('18', 'ตู้เย็น', '1', '2'), ('19', 'พัดลม', '1', '2'), ('20', 'โน๊ตบุค', '2', '2'), ('21', 'แท็บเล็ต', '2', '2'), ('22', 'หน้าจอคอมพิวเตอร์', '2', '2'), ('23', 'คีย์บอร์ดคอมพิวเตอร์', '2', '2'), ('24', 'กระดาษ A5', '3', '1'), ('25', 'เก้าอี้', '5', '2'), ('26', 'เมาส์', '2', '1'), ('27', 'โปรเจคเตอร์', '2', '2'), ('28', 'แผ่นซีดี/ดีวีดี', '1', '1'), ('29', 'แฟ้มเอกสาร ขนาด  A4', '3', '1'), ('30', 'หนังสือ การโปรแกรมมิ่ง JAVA เบื้องต้น', '8', '2'), ('31', 'หนังสือ กาออกแบบและวิเคราห์ระบบ', '8', '1'), ('32', 'หนังสือ ระบบฐานข้อมูล', '8', '2'), ('33', 'หนังสือ เขียนโปรแกรมเบื้องต้น PHP&MYSQL', '8', '1'), ('34', 'เครื่องปริ้นเตอร์ injet', '2', '2'), ('35', 'อุปกรณ์สื่อสาร', '1', '2'), ('36', 'เครื่องคอมพิวเตอร์ SERVER', '1', '1'), ('37', 'ปากกาเขียนทั่วไป', '3', '1'), ('38', 'คลิปหนีบกระดาษ', '3', '1'), ('39', 'ระบบปฎิบัติการ Windows xp', '7', '2'), ('40', 'ระบบปฎิบัติการ Windows 7', '7', '2'), ('41', 'Adobe Photoshop CS 5.5', '7', '2'), ('42', 'Adobe dreamweaver cs5', '7', '2'), ('43', 'DVD สอนเขียนโปรแกรม ภาษา C', '4', '1');
COMMIT;

-- ----------------------------
--  Table structure for `material_item_status`
-- ----------------------------
DROP TABLE IF EXISTS `material_item_status`;
CREATE TABLE `material_item_status` (
  `status_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_item_status`
-- ----------------------------
BEGIN;
INSERT INTO `material_item_status` VALUES ('1', 'ปกติ'), ('2', 'หมดอายุการใช้งาน'), ('3', 'จำหน่าย/ส่งคืนครุภัณฑ์');
COMMIT;

-- ----------------------------
--  Table structure for `material_items`
-- ----------------------------
DROP TABLE IF EXISTS `material_items`;
CREATE TABLE `material_items` (
  `code` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `register_date` datetime DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `material_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT '1',
  `barcode` varchar(255) NOT NULL,
  `status_id` int(11) NOT NULL DEFAULT '1',
  `exp_date` date NOT NULL,
  `exp_detail` text CHARACTER SET utf8 NOT NULL,
  `serial_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_items`
-- ----------------------------
BEGIN;
INSERT INTO `material_items` VALUES ('1200114', null, '', '76', '1', '120800000043', '2', '2012-08-20', '', ''), ('1200115', null, '', '76', '1', '120800000044', '1', '0000-00-00', '', ''), ('1200116', null, '', '76', '1', '120800000045', '1', '0000-00-00', '', ''), ('1200117', null, '', '76', '1', '120800000046', '1', '0000-00-00', '', ''), ('1200118', null, '', '76', '1', '120800000047', '1', '0000-00-00', '', ''), ('1200119', null, '', '76', '1', '120800000048', '1', '0000-00-00', '', ''), ('1200120', null, '', '76', '1', '120800000049', '1', '0000-00-00', '', ''), ('1200121', null, '', '76', '1', '120800000050', '1', '0000-00-00', '', ''), ('1200122', null, '', '76', '1', '120800000051', '1', '0000-00-00', '', ''), ('1200123', null, '', '76', '1', '120800000052', '1', '0000-00-00', '', ''), ('1200124', null, '', '76', '1', '120800000053', '1', '0000-00-00', '', ''), ('1200125', null, '', '76', '1', '120800000054', '1', '0000-00-00', '', ''), ('1200126', null, '', '76', '1', '120800000055', '1', '0000-00-00', '', ''), ('1200127', null, '', '76', '1', '120800000056', '1', '0000-00-00', '', ''), ('1200128', null, '', '76', '1', '120800000057', '1', '0000-00-00', '', ''), ('1200129', null, '', '76', '1', '120800000058', '1', '0000-00-00', '', ''), ('1200130', null, '', '76', '1', '120800000059', '1', '0000-00-00', '', ''), ('1200131', null, '', '76', '1', '120800000060', '1', '0000-00-00', '', ''), ('1200132', null, '', '76', '1', '120800000061', '1', '0000-00-00', '', ''), ('1200133', null, '', '76', '1', '120800000062', '1', '0000-00-00', '', ''), ('1200134', null, '', '76', '1', '120800000063', '1', '0000-00-00', '', ''), ('1200135', null, '', '76', '1', '120800000064', '1', '0000-00-00', '', ''), ('1200136', null, '', '76', '1', '120800000065', '1', '0000-00-00', '', ''), ('1200137', null, '', '76', '1', '120800000066', '1', '0000-00-00', '', ''), ('1200138', null, '', '76', '1', '120800000067', '1', '0000-00-00', '', ''), ('1200139', null, '', '76', '1', '120800000068', '1', '0000-00-00', '', ''), ('1200140', null, '', '76', '1', '120800000069', '1', '0000-00-00', '', ''), ('1200141', null, '', '76', '1', '120800000070', '1', '0000-00-00', '', ''), ('1200142', null, '', '76', '1', '120800000071', '1', '0000-00-00', '', ''), ('1200143', null, '', '76', '1', '120800000072', '1', '0000-00-00', '', ''), ('1200144', null, '', '76', '1', '120800000073', '1', '0000-00-00', '', ''), ('1200145', null, '', '76', '1', '120800000074', '1', '0000-00-00', '', ''), ('1200146', null, '', '76', '1', '120800000075', '1', '0000-00-00', '', ''), ('1200147', null, '', '76', '1', '120800000076', '1', '0000-00-00', '', ''), ('1200148', null, '', '76', '1', '120800000077', '1', '0000-00-00', '', ''), ('1200149', null, '', '76', '1', '120800000078', '1', '0000-00-00', '', ''), ('1200150', null, '', '76', '1', '120800000079', '1', '0000-00-00', '', ''), ('1200151', null, '', '76', '1', '120800000080', '1', '0000-00-00', '', ''), ('1200152', null, '', '76', '1', '120800000081', '1', '0000-00-00', '', ''), ('1200153', null, '', '76', '1', '120800000082', '1', '0000-00-00', '', ''), ('1200164', null, '', '78', '1', '120800000093', '1', '0000-00-00', '', ''), ('1200165', null, '', '78', '1', '120800000094', '1', '0000-00-00', '', ''), ('1200166', null, '', '78', '1', '120800000095', '1', '0000-00-00', '', ''), ('1200167', null, '', '78', '1', '120800000096', '1', '0000-00-00', '', ''), ('1200168', null, '', '78', '1', '120800000097', '1', '0000-00-00', '', ''), ('1200169', null, '', '78', '1', '120800000098', '1', '0000-00-00', '', ''), ('1200170', null, '', '78', '1', '120800000099', '1', '0000-00-00', '', ''), ('1200171', null, '', '78', '1', '120800000100', '1', '0000-00-00', '', ''), ('1200172', null, '', '78', '1', '120800000101', '1', '0000-00-00', '', ''), ('1200173', null, '', '78', '1', '120800000102', '1', '0000-00-00', '', '');
COMMIT;

-- ----------------------------
--  Table structure for `material_registers`
-- ----------------------------
DROP TABLE IF EXISTS `material_registers`;
CREATE TABLE `material_registers` (
  `material_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัส วัสดุครุภัณฑ์',
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชื่อวัสดุครภัณฑ์',
  `model` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL COMMENT 'หมวด',
  `type_id` int(11) DEFAULT NULL COMMENT 'เป็น วัสดุ หรือ ครุภัณฑ์ มี ค่า 1 = วัสดุ  2 = ครุถัณฑ์',
  `buy_date` date DEFAULT NULL,
  `buy_price` varchar(255) DEFAULT NULL,
  `brand` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `warranty` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'เงือนไขการรับประกัน',
  `detail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `responsible` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ผู้รับผิดชอบ',
  `thumbnail` varchar(255) DEFAULT NULL COMMENT 'เก็บรูปที่ 1',
  `create_date` varchar(255) DEFAULT NULL COMMENT 'วันที่กรอก ข้อมูล',
  `last_modify` date DEFAULT NULL COMMENT 'วันที่แก้ไข้ข้อมูลล่าสุด',
  `year` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ปีการ ศึกษา',
  `budget_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `ref_order_item_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`material_id`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_registers`
-- ----------------------------
BEGIN;
INSERT INTO `material_registers` VALUES ('76', 'หนังสือเรียน/หนังสือเรืยน สารสนเทศเพื่อชีวิต', '', '10', '2', '2012-08-17', '200', 'se-ed', '', '', null, '39bbbb532bc2993ded9e8fca579b9bd4.png', '12/08/19 20:11:31', '2012-08-19', null, '1', '1', '0'), ('78', 'อุปกรณ์สำนักงาน/ปากกาเขียนทั่วไป', '', '37', '1', '2012-08-17', '5', 'ตราม้า', '', '', null, '2e039cb9b004f695c98e8ccca45105cd.png', '12/08/20 12:31:49', '2012-08-20', null, '1', '1', '0');
COMMIT;

-- ----------------------------
--  Table structure for `material_request_items`
-- ----------------------------
DROP TABLE IF EXISTS `material_request_items`;
CREATE TABLE `material_request_items` (
  `mr_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `mr_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`mr_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=58 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_request_items`
-- ----------------------------
BEGIN;
INSERT INTO `material_request_items` VALUES ('57', '52', '40', '10'), ('55', '51', '4', '10'), ('54', '50', '1', '20'), ('56', '52', '10', '37');
COMMIT;

-- ----------------------------
--  Table structure for `material_requests`
-- ----------------------------
DROP TABLE IF EXISTS `material_requests`;
CREATE TABLE `material_requests` (
  `mr_id` int(255) NOT NULL AUTO_INCREMENT,
  `detail` text NOT NULL,
  `place_id` int(255) NOT NULL,
  `user_id` int(255) NOT NULL,
  `create_date` date NOT NULL,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`mr_id`)
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `material_requests`
-- ----------------------------
BEGIN;
INSERT INTO `material_requests` VALUES ('50', '', '2', '9', '2012-08-17', '2'), ('51', '', '1', '9', '2012-08-17', '1'), ('52', '', '1', '9', '2012-08-17', '1');
COMMIT;

-- ----------------------------
--  Table structure for `material_types`
-- ----------------------------
DROP TABLE IF EXISTS `material_types`;
CREATE TABLE `material_types` (
  `type_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชือ ชนิด material มี 2 อัน วัสดุ กับ ครุภัณฑ์',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_types`
-- ----------------------------
BEGIN;
INSERT INTO `material_types` VALUES ('1', 'วัสดุ'), ('2', 'ครุภัณฑ์');
COMMIT;

-- ----------------------------
--  Table structure for `meterial_base_categories`
-- ----------------------------
DROP TABLE IF EXISTS `meterial_base_categories`;
CREATE TABLE `meterial_base_categories` (
  `cat_base_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_base_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`cat_base_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `meterial_base_categories`
-- ----------------------------
BEGIN;
INSERT INTO `meterial_base_categories` VALUES ('1', 'เครื่องใช้ไฟฟ้า', '4'), ('2', 'เครื่องคอมพิวเตอร์/และอุปกรณ์', '1'), ('3', 'อุปกรณ์สำนักงาน', '2'), ('4', 'สือการเรียนการสอน', '3'), ('5', 'เฟอร์นิเจอร์ลอยตัว', '5'), ('6', 'อื่นๆ', '20'), ('7', 'ซอฟแวร์', '6'), ('8', 'หนังสือเรียน', '2');
COMMIT;

-- ----------------------------
--  Table structure for `order_items`
-- ----------------------------
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `order_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `detail` text CHARACTER SET utf8 NOT NULL,
  `amount` int(11) NOT NULL,
  `price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `category_id` int(11) NOT NULL,
  `receive_amount` int(11) DEFAULT NULL,
  `receive_date` datetime DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `order_items`
-- ----------------------------
BEGIN;
INSERT INTO `order_items` VALUES ('7', '9', '', '1', '100', '6', '1', null), ('8', '9', '', '1', '100', '14', '1', null), ('9', '9', '', '2', '80', '17', '2', null), ('10', '9', '', '2', '20', '24', '2', null), ('11', '9', 'xxx', '5', '125', '29', '5', null), ('12', '11', 'macbook pro', '1', '40000', '2', '1', null), ('13', '11', 'HP 1010', '1', '3500', '4', '1', null), ('20', '14', 'ปากกาเคมี สีน้ำเงิน', '1', '25', '6', '1', null), ('21', '15', '', '40', '200', '10', '40', null), ('22', '15', '', '10', '5', '37', '10', null);
COMMIT;

-- ----------------------------
--  Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `create_date` datetime NOT NULL,
  `remark` text CHARACTER SET utf8 NOT NULL,
  `budget_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `orders`
-- ----------------------------
BEGIN;
INSERT INTO `orders` VALUES ('9', '2', '2012-06-03 19:59:02', 'xxxx', '2', '2', '1'), ('14', '2', '2012-08-11 12:26:46', 'อาจารย์ สุวรรณา', '2', '2', '1'), ('11', '2', '2012-06-04 12:31:00', 'ซื้อคอมใหม่ 1  ชุด', '1', '1', '2'), ('15', '2', '2012-08-17 16:16:43', '', '2', '1', '1');
COMMIT;

-- ----------------------------
--  Table structure for `place`
-- ----------------------------
DROP TABLE IF EXISTS `place`;
CREATE TABLE `place` (
  `place_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัส primary key',
  `place_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชือ่ห้อง หรือ สถานที่ ',
  `remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'หมายเหตุ / รายละเอียด',
  PRIMARY KEY (`place_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `place`
-- ----------------------------
BEGIN;
INSERT INTO `place` VALUES ('1', 'ห้องเรียน 411', null), ('2', 'ห้องพักอาจารย์', null), ('3', 'ห้องเรียน 542', null), ('4', 'ห้องเรียน 553', null), ('5', 'ห้องเรียน 543', null), ('6', 'ห้องเรียน 441', null);
COMMIT;

-- ----------------------------
--  Table structure for `ref_code`
-- ----------------------------
DROP TABLE IF EXISTS `ref_code`;
CREATE TABLE `ref_code` (
  `ref_code` varchar(255) NOT NULL,
  `ref_no` int(11) NOT NULL,
  `detail` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `ref_code`
-- ----------------------------
BEGIN;
INSERT INTO `ref_code` VALUES ('M2', '174', 'เลขที่ วัสดุ & barcode'), ('M1', '14', 'เลขที่ ครุภัณ & barcode'), ('M', '29', 'materail'), ('F', '14', 'รหัสส่งซ่อม'), ('M', '29', 'materail'), ('F', '14', 'รหัสส่งซ่อม'), ('BARCODE', '103', 'BARCODE');
COMMIT;

-- ----------------------------
--  Table structure for `report_builders`
-- ----------------------------
DROP TABLE IF EXISTS `report_builders`;
CREATE TABLE `report_builders` (
  `rb_id` int(11) NOT NULL AUTO_INCREMENT,
  `sql_text` text,
  `report_name` varchar(255) DEFAULT NULL,
  `webservice_method` varchar(11) DEFAULT NULL,
  `is_xml` int(11) unsigned zerofill DEFAULT NULL,
  `remark` text,
  `param` text,
  PRIMARY KEY (`rb_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
--  Records of `report_builders`
-- ----------------------------
BEGIN;
INSERT INTO `report_builders` VALUES ('10', 'select * from controllers', 'Controllers All', 'controller_', null, '', ''), ('9', 'select * from users', 'All User', 'all_user', null, '', '');
COMMIT;

-- ----------------------------
--  Table structure for `roles`
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `controller_access` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- ----------------------------
--  Records of `roles`
-- ----------------------------
BEGIN;
INSERT INTO `roles` VALUES ('1', 'เจ้าหน้าที่ครุภัณฑ์', '1,2,3,4,5,6,7,8,31,32,33,34,35,36,37,38,39,40,64,65,75,89,91,96,93,110,29,30,49,41,42,92,45,46,90,111,79,80,94,97,98,84,85,86,87,95,109,99,100,101,102,103,104,105,106,107,108'), ('2', 'ผู้บริหาร', '93,47,48,51,52,53,54,55,56,57,58,60,61,62,49,41,42,92,94,84'), ('3', 'อาจารย์สอน', '65,93,41,42,92,66,67,79,80,94,97,98,84,85,86,87'), ('4', 'ผู้ดูแลระบบ', '1,2,3,4,5,6,7,8,41,42'), ('5', 'เจ้าหน้าที่ซ่อมบำรุง', '65,91,93,41,42,92,46,66,67,68,69,70,72,73,77,78,83,90');
COMMIT;

-- ----------------------------
--  Table structure for `sub_allocation_items`
-- ----------------------------
DROP TABLE IF EXISTS `sub_allocation_items`;
CREATE TABLE `sub_allocation_items` (
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  `sub_alloc_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date` date NOT NULL,
  `return_date` date NOT NULL,
  `allocation_item_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `return_detail` text NOT NULL,
  `is_return` int(11) NOT NULL,
  PRIMARY KEY (`sub_alloc_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `sub_allocation_items`
-- ----------------------------
BEGIN;
INSERT INTO `sub_allocation_items` VALUES ('1200114', '2', '2012-08-19', '2012-08-19', '1', '1', '', '1'), ('1200115', '3', '2012-08-19', '2012-08-19', '1', '1', '', '1'), ('1200116', '4', '2012-08-19', '2012-08-19', '1', '1', '', '1'), ('1200117', '5', '2012-08-19', '0000-00-00', '1', '1', '', '0'), ('1200118', '7', '2012-08-19', '0000-00-00', '1', '1', '', '0');
COMMIT;

-- ----------------------------
--  Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `firstname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `lastname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `role_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชนิดผู้ใช้งาน',
  `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- ----------------------------
--  Records of `users`
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES ('1', 'wirachat', 'e10adc3949ba59abbe56e057f20f883e', 'วิรฉัตร', 'สุขสวัสดิ์', 'wirachat2you@hotmail.com', '0842188408', '4', 'Developer'), ('2', 'staff', 'e10adc3949ba59abbe56e057f20f883e', 'เสรี', 'ทองแตง', 'staff@itbsru.net', '0842188408', '1', 'เจ้าหน้าที่วัสดุครุภัณฑ์'), ('8', 'manager', 'e10adc3949ba59abbe56e057f20f883e', 'อาจารย์นัยนพัศ', 'อินจวงจิรกิตต์', 'itbsru@bsru.ac.th', '08411919191', '2', 'อาจารย์ประจำ'), ('9', 'teacher', 'e10adc3949ba59abbe56e057f20f883e', 'เจษฐา', 'รักไทย', 'aaa@hotmail.com', '08411919191', '3', 'อาจารย์ประจำ'), ('10', 'kowit', 'e10adc3949ba59abbe56e057f20f883e', 'kowit', 'nueangjumnong', 'test@test.com', '088112233', '3', 'staff'), ('11', 'admin', 'e10adc3949ba59abbe56e057f20f883e', 'เจ้าหน้าที่ดูแลระบบ', 'จงสืบพันธ์', 'admin@bsru.ac.th', '0842188408', '4', 'ผู้ดูแลระบบ'), ('12', 'labboy', 'e10adc3949ba59abbe56e057f20f883e', 'labboy', 'goodjob', 'labboy@itbsru.net', '064547353', '5', 'เจ้าหน้าที่ซ่อมบำรุง'), ('13', 'asdf', 'e10adc3949ba59abbe56e057f20f883e', 'fdsa', 'asdf', 'asdf@gasdf.com', '203948', '3', '');
COMMIT;

-- ----------------------------
--  Table structure for `year_list`
-- ----------------------------
DROP TABLE IF EXISTS `year_list`;
CREATE TABLE `year_list` (
  `year_name` varchar(255) NOT NULL,
  `year_value` varchar(255) NOT NULL,
  PRIMARY KEY (`year_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `year_list`
-- ----------------------------
BEGIN;
INSERT INTO `year_list` VALUES ('2550', '2007'), ('2551', '2008'), ('2552', '2009'), ('2553', '2010'), ('2554', '2011'), ('2555', '2012');
COMMIT;

-- ----------------------------
--  View structure for `allocation_item_return_views`
-- ----------------------------
DROP VIEW IF EXISTS `allocation_item_return_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `allocation_item_return_views` AS select `sub_allocation_items`.`code` AS `code`,`sub_allocation_items`.`allocation_item_id` AS `allocation_item_id`,`sub_allocation_items`.`amount` AS `amount`,`sub_allocation_items`.`return_detail` AS `return_detail`,`sub_allocation_items`.`is_return` AS `is_return`,date_format(`sub_allocation_items`.`create_date`,'%d/%m/%Y') AS `create_date`,`material_item_views`.`name` AS `name`,`material_item_views`.`barcode` AS `barcode`,`material_item_views`.`register_date` AS `register_date`,`allocation_items`.`allocation_id` AS `allocation_id`,date_format(`sub_allocation_items`.`return_date`,'%d/%m/%Y') AS `return_date`,date_format(`sub_allocation_items`.`create_date`,'%d/%m/%Y') AS `create_date2`,`sub_allocation_items`.`sub_alloc_item_id` AS `sub_alloc_item_id` from ((`sub_allocation_items` join `material_item_views` on((`sub_allocation_items`.`code` = `material_item_views`.`code`))) join `allocation_items` on((`allocation_items`.`allocation_item_id` = `sub_allocation_items`.`allocation_item_id`)));

-- ----------------------------
--  View structure for `allocation_item_views`
-- ----------------------------
DROP VIEW IF EXISTS `allocation_item_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `allocation_item_views` AS select `allocation_items`.`category_id` AS `category_id`,`allocation_items`.`allocation_item_id` AS `allocation_item_id`,`allocation_items`.`return` AS `return`,`allocation_items`.`return_date` AS `return_date`,`allocation_items`.`remark` AS `remark`,`allocation_items`.`amount` AS `amount`,`allocation_items`.`allocation_id` AS `allocation_id`,`allocation_items`.`code` AS `code`,`category_views`.`cat_base_name` AS `cat_base_name`,`category_views`.`cat_base_id` AS `cat_base_id`,`category_views`.`type_name` AS `type_name`,`category_views`.`type_id` AS `type_id`,`category_views`.`category_name` AS `category_name` from (`allocation_items` join `category_views` on((`allocation_items`.`category_id` = `category_views`.`category_id`)));

-- ----------------------------
--  View structure for `allocation_views`
-- ----------------------------
DROP VIEW IF EXISTS `allocation_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `allocation_views` AS select `users`.`firstname` AS `firstname`,`users`.`lastname` AS `lastname`,`allocations`.`allocation_id` AS `allocation_id`,lpad(`allocations`.`allocation_id`,5,'0') AS `code`,date_format(`allocations`.`allocation_date`,'%d/%m/%Y') AS `allocation_date`,`allocations`.`user_id` AS `user_id`,`allocations`.`detail` AS `detail`,`allocations`.`return_date` AS `return_date`,`allocations`.`allocation_type` AS `allocation_type`,`users`.`position` AS `position`,`users`.`tel` AS `tel`,`roles`.`role_name` AS `role_name`,`place`.`place_name` AS `place_name` from (((`allocations` left join `users` on((`allocations`.`user_id` = `users`.`user_id`))) join `place` on((`allocations`.`place_id` = `place`.`place_id`))) join `roles` on((`users`.`role_id` = `roles`.`role_id`))) where 1;

-- ----------------------------
--  View structure for `category_views`
-- ----------------------------
DROP VIEW IF EXISTS `category_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `category_views` AS select `material_categories`.`category_name` AS `category_name`,`material_categories`.`category_id` AS `category_id`,`material_categories`.`cat_base_id` AS `cat_base_id`,`meterial_base_categories`.`cat_base_name` AS `cat_base_name`,`meterial_base_categories`.`sort_order` AS `sort_order`,`material_types`.`type_name` AS `type_name`,`material_categories`.`type_id` AS `type_id` from ((`material_categories` join `meterial_base_categories` on((`material_categories`.`cat_base_id` = `meterial_base_categories`.`cat_base_id`))) join `material_types` on((`material_categories`.`type_id` = `material_types`.`type_id`)));

-- ----------------------------
--  View structure for `inventory_view`
-- ----------------------------
DROP VIEW IF EXISTS `inventory_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory_view` AS select `material_registers`.`material_id` AS `material_id`,`material_registers`.`name` AS `name`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,`material_registers`.`type_id` AS `type_id`,`material_registers`.`buy_date` AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`company_name` AS `company_name`,`company`.`tel` AS `tel`,`company`.`address` AS `address`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amout_total` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `inventory_views`
-- ----------------------------
DROP VIEW IF EXISTS `inventory_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory_views` AS select lpad(`material_registers`.`material_id`,6,0) AS `code`,`material_registers`.`material_id` AS `material_id`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,date_format(`material_registers`.`buy_date`,'%d/%m/%Y') AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,date_format(`material_registers`.`create_date`,'%d/%m/%Y') AS `create_date2`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`tel` AS `tel`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amount_total`,`material_categories`.`category_name` AS `category_name`,`material_registers`.`name` AS `name`,`company`.`company_name` AS `company_name`,`company`.`address` AS `address`,`material_registers`.`type_id` AS `type_id`,(select coalesce(sum(`im`.`allocation_total`),0) AS `COALESCE(sum(allocation_total ),0)` from `item_amount_views` `im` where (`im`.`material_id` = `material_registers`.`material_id`)) AS `allocation_total`,date_format(`material_registers`.`buy_date`,'%Y') AS `year_value`,`material_registers`.`buy_date` AS `buy_date2` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `item_allocation_views`
-- ----------------------------
DROP VIEW IF EXISTS `item_allocation_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `item_allocation_views` AS select `sub_allocation_items`.`code` AS `code`,date_format(`sub_allocation_items`.`create_date`,'%d/%m/%Y') AS `create_date`,`sub_allocation_items`.`amount` AS `amount`,date_format(`sub_allocation_items`.`return_date`,'%d/%m/%Y') AS `return_date`,`allocation_views`.`firstname` AS `firstname`,`allocation_views`.`lastname` AS `lastname`,`allocation_views`.`user_id` AS `user_id`,`sub_allocation_items`.`is_return` AS `is_return`,`sub_allocation_items`.`return_detail` AS `return_detail`,`allocation_views`.`allocation_id` AS `allocation_id`,`allocations`.`place_id` AS `place_id`,`place`.`place_name` AS `place_name` from ((((`sub_allocation_items` join `allocation_items` on((`sub_allocation_items`.`allocation_item_id` = `allocation_items`.`allocation_item_id`))) left join `allocation_views` on((`allocation_items`.`allocation_id` = `allocation_views`.`allocation_id`))) left join `allocations` on((`allocation_views`.`allocation_id` = `allocations`.`allocation_id`))) left join `place` on((`allocations`.`place_id` = `place`.`place_id`)));

-- ----------------------------
--  View structure for `item_amount_views`
-- ----------------------------
DROP VIEW IF EXISTS `item_amount_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `item_amount_views` AS select `mi`.`code` AS `code`,`mi`.`amount` AS `amount`,`mi`.`material_id` AS `material_id`,(select coalesce(sum(`si`.`amount`),0) AS `COALESCE(sum(amount),0)` from `sub_allocation_items` `si` where ((`si`.`code` = `mi`.`code`) and (`si`.`is_return` = 0))) AS `allocation_total` from `material_items` `mi`;

-- ----------------------------
--  View structure for `maintain_views`
-- ----------------------------
DROP VIEW IF EXISTS `maintain_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `maintain_views` AS select `maintains`.`code` AS `code`,`maintains`.`subject` AS `subject`,`maintains`.`remark` AS `remark`,date_format(`maintains`.`create_date`,'%d/%m/%Y') AS `create_date`,`maintains`.`user_id` AS `user_id`,`maintains`.`maintain_id` AS `maintain_id`,lpad(`maintains`.`maintain_id`,4,'0') AS `m_code`,`inventory_view`.`name` AS `name`,`inventory_view`.`model` AS `model`,`inventory_view`.`thumbnail` AS `thumbnail`,`inventory_view`.`buy_date` AS `buy_date`,`inventory_view`.`brand` AS `brand`,`inventory_view`.`detail` AS `detail`,`material_items`.`barcode` AS `barcode`,`users`.`firstname` AS `firstname`,`users`.`lastname` AS `lastname`,date_format(`maintains`.`finish_date`,'%d/%m/%Y') AS `finish_date`,`maintains`.`price` AS `price`,`maintains`.`remark2` AS `remark2`,`maintains`.`warranty` AS `warranty`,`maintains`.`status_id` AS `status_id`,`maintain_status`.`status_name` AS `status_name` from ((((`maintains` join `material_items` on((`maintains`.`code` = `material_items`.`code`))) left join `inventory_view` on((`material_items`.`material_id` = `inventory_view`.`material_id`))) left join `users` on((`maintains`.`user_id` = `users`.`user_id`))) left join `maintain_status` on((`maintains`.`status_id` = `maintain_status`.`status_id`)));

-- ----------------------------
--  View structure for `material_item_views`
-- ----------------------------
DROP VIEW IF EXISTS `material_item_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `material_item_views` AS select `material_items`.`code` AS `code`,`material_items`.`register_date` AS `register_date`,`material_items`.`remark` AS `remark`,`material_items`.`material_id` AS `material_id`,`material_items`.`amount` AS `amount`,`material_items`.`barcode` AS `barcode`,`material_items`.`status_id` AS `status_id`,date_format(`material_items`.`exp_date`,'%d/%m/%Y') AS `exp_date`,`material_items`.`exp_detail` AS `exp_detail`,`material_registers`.`name` AS `name`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,date_format(`material_registers`.`buy_date`,'%d/%m/%Y') AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,date_format(`material_registers`.`create_date`,'%d/%m/%Y') AS `create_date2`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`year` AS `year`,`material_registers`.`budget_id` AS `budget_id`,`meterial_base_categories`.`cat_base_name` AS `cat_base_name`,`meterial_base_categories`.`cat_base_id` AS `cat_base_id`,`material_categories`.`category_name` AS `category_name`,`material_types`.`type_name` AS `type_name`,`material_items`.`serial_code` AS `serial_code`,`material_registers`.`create_date` AS `create_date`,`material_item_status`.`name` AS `m_status_name` from (((((`material_items` left join `material_registers` on((`material_items`.`material_id` = `material_registers`.`material_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `meterial_base_categories` on((`material_categories`.`cat_base_id` = `meterial_base_categories`.`cat_base_id`))) left join `material_item_status` on((`material_items`.`status_id` = `material_item_status`.`status_id`)));

-- ----------------------------
--  View structure for `material_views`
-- ----------------------------
DROP VIEW IF EXISTS `material_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `material_views` AS select lpad(`material_registers`.`material_id`,6,0) AS `code`,`material_registers`.`material_id` AS `material_id`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,date_format(`material_registers`.`buy_date`,'%d/%m/%Y') AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,date_format(`material_registers`.`create_date`,'%d/%m/%Y') AS `create_date2`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`tel` AS `tel`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amount_total`,`material_categories`.`category_name` AS `category_name`,`material_registers`.`name` AS `name`,`company`.`company_name` AS `company_name`,`company`.`address` AS `address`,`material_registers`.`type_id` AS `type_id`,(select coalesce(sum(`im`.`allocation_total`),0) AS `COALESCE(sum(allocation_total ),0)` from `item_amount_views` `im` where (`im`.`material_id` = `material_registers`.`material_id`)) AS `allocation_total`,`material_registers`.`buy_date` AS `buy_date2` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `material_views2`
-- ----------------------------
DROP VIEW IF EXISTS `material_views2`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `material_views2` AS select `material_registers`.`material_id` AS `material_id`,`material_registers`.`name` AS `name`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,`material_registers`.`type_id` AS `type_id`,`material_registers`.`buy_date` AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`company_name` AS `company_name`,`company`.`tel` AS `tel`,`company`.`address` AS `address`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `order_item_views`
-- ----------------------------
DROP VIEW IF EXISTS `order_item_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `order_item_views` AS select `order_items`.`order_item_id` AS `order_item_id`,`order_items`.`order_id` AS `order_id`,`order_items`.`detail` AS `detail`,`order_items`.`amount` AS `amount`,`order_items`.`price` AS `price`,`order_items`.`category_id` AS `category_id`,`category_views`.`cat_base_name` AS `cat_base_name`,`category_views`.`type_name` AS `type_name`,`category_views`.`type_id` AS `type_id`,`category_views`.`category_name` AS `category_name`,`order_items`.`receive_amount` AS `receive_amount`,`order_items`.`receive_date` AS `receive_date`,`order_views`.`year` AS `year`,`order_views`.`budget_name` AS `budget_name`,`order_views`.`budget_id` AS `budget_id`,`order_views`.`company_id` AS `company_id`,`order_views`.`firstname` AS `firstname`,`order_views`.`lastname` AS `lastname`,`order_views`.`user_id` AS `user_id`,`order_views`.`create_date` AS `create_date`,`order_views`.`remark` AS `remark` from ((`order_items` join `category_views` on((`order_items`.`category_id` = `category_views`.`category_id`))) join `order_views` on((`order_items`.`order_id` = `order_views`.`order_id`)));

-- ----------------------------
--  View structure for `order_views`
-- ----------------------------
DROP VIEW IF EXISTS `order_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `order_views` AS select `users`.`firstname` AS `firstname`,`users`.`lastname` AS `lastname`,`budgets`.`budget_name` AS `budget_name`,`budgets`.`year` AS `year`,lpad(`orders`.`order_id`,5,0) AS `code`,`orders`.`order_id` AS `order_id`,`orders`.`user_id` AS `user_id`,date_format(`orders`.`create_date`,'%d/%m/%Y') AS `create_date`,`orders`.`remark` AS `remark`,`orders`.`budget_id` AS `budget_id`,`orders`.`company_id` AS `company_id`,`orders`.`type_id` AS `type_id`,`company`.`company_name` AS `company_name`,`company`.`address` AS `address`,`company`.`tel` AS `tel`,`material_types`.`type_name` AS `type_name` from ((((`orders` left join `users` on((`orders`.`user_id` = `users`.`user_id`))) left join `budgets` on((`orders`.`budget_id` = `budgets`.`budget_id`))) left join `company` on((`orders`.`company_id` = `company`.`company_id`))) left join `material_types` on((`orders`.`type_id` = `material_types`.`type_id`)));

-- ----------------------------
--  View structure for `request_item_views`
-- ----------------------------
DROP VIEW IF EXISTS `request_item_views`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `request_item_views` AS select `material_request_items`.`amount` AS `amount`,`material_request_items`.`mr_item_id` AS `mr_item_id`,`material_request_items`.`mr_id` AS `mr_id`,`material_request_items`.`category_id` AS `category_id`,`material_categories`.`category_name` AS `category_name`,`material_categories`.`cat_base_id` AS `cat_base_id` from (`material_request_items` left join `material_categories` on((`material_request_items`.`category_id` = `material_categories`.`category_id`)));

SET FOREIGN_KEY_CHECKS = 1;
